package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBObject {
	/**
	 * This is to test out and see if we can get a connection to the database.
	 * 
	 * @param args
	 */
	Connection connection;

	public DBObject() throws SQLException {
		connection = connect();
	}

	public ResultSet getTable(String tableName) throws SQLException {
		ResultSet resultSet = null;
		Statement statement = null;
		

		try {
			if (tableName.equals("Yearly Budget")) {
				statement = connection.createStatement();
				resultSet = statement
						.executeQuery("Select  cname As \"Category\", name As \"Subcategory\" , fiscalyear, previousyear AS \" Previous Year \", currentyearamount AS \"Budget\", "
								+ "actualamount AS \"Actual Non-Lobbying\",  lobbyingexp As \"Actual Lobbying\", actualamount As \"Actual As of \"From "
								+ "belongstocategory Right Join (Select t1.name, fiscalyear, previousyear, actualamount, currentyearamount, lobbyingexp "
								+ " from ((select id, name from budgethassubcategory) T1 Join Budget on Budget.id = T1.id) Join "
								+ "subcategory on T1.name = subcategory.name) T2 on T2.name = belongstocategory.sname Order By(cname)");
			}

			else if (tableName.equals("Membership Types")) {
				statement = connection.createStatement();
				resultSet = statement
						.executeQuery("Select *  From membershiptype ");
			} else if (tableName.equals("Vendors")) {
				statement = connection.createStatement();
				resultSet = statement.executeQuery("Select *  From Vendor ");
			} else if (tableName.equals("Members")) {
				statement = connection.createStatement();
				resultSet = statement
						.executeQuery("Select firstname, lastname, address, city, state, zip, zip4, phone,  Members.email, pay.duespaid As \"Membership Payed\", HasMembership.type FROM  "
								+ "(Members Join Contributors on Members.email = Contributors.email  JOIN HasMembership on Members.email = HasMembership.email)"
								+ "  Left Join Pay on pay.email = members.email ");

			} else if (tableName.equals("Nonmembers")) {
				statement = connection.createStatement();
				resultSet = statement
						.executeQuery("Select name, address, city, state, zip, zip4, phone, nonmembers.email, donate.amount \"Amount Donated \" FROM (nonmembers Join Contributors on nonmembers.email = Contributors.email) left Join Donate on nonmembers.email = donate.email");
			} else if (tableName.equals("Transactions")) {
				statement = connection.createStatement();

				resultSet = statement
						.executeQuery("Select Transactions.id, name, date, action, checknumber as \"CHECK\", payeepayer, payment, deposit, memo from "
								+ "Transactions Join transhassub On transactions.id = transhassub.id order by id asc");

			} else if (tableName.equals("Accounts")) {
				statement = connection.createStatement();
				resultSet = statement
						.executeQuery("Select accountnumber as \"ID\", CAST(balance + 0.005 AS DECIMAL(15,2)) as \"Balance\", type from accounts");
			}
			else if(tableName.equals("Monthly Breakdown")){
				statement = connection.createStatement();
				resultSet = statement.executeQuery("Select name, Sum(Deposit) from transactions Join transhascat on transactions.id = transhascat.id group by(name)");
			}else if(tableName.equals("Categories")){
				statement = connection.createStatement();
				resultSet = statement.executeQuery("Select cname as \"Category\",sname as \"Subcategory\", lobbyingexp from belongstocategory join subcategory on sname = name order by cname asc");
			}else if(tableName.equals("Budget")){
				statement = connection.createStatement();
				resultSet = statement.executeQuery("Select budgethascategory.name as \"Category\", budgethassubcategory.name as \"Subcategory\", fiscalyear, previousyear, currentyearamount, actualamount from budget left join budgethassubcategory on budget.id = budgethassubcategory.id left join budgethascategory on budget.id = budgethascategory.id order by budgethascategory.name asc");
			}
		} catch (Exception e) {
			System.out.println(e);
		}
		
		return resultSet;

	}
	public ResultSet executeQuery(String myStatement) throws SQLException {
		Statement statement = null;
		ResultSet mySet = null;
		System.out.println(myStatement);

		try {
			statement = connection.createStatement();
			mySet = statement.executeQuery(myStatement);
		} catch (Exception e) {
			System.out.println(e);
		}
		
		System.out.println("Done");
		return mySet;
	}

	public void executeAddOrEdit(String myStatement) throws SQLException {
		Statement statement = null;
		System.out.println(myStatement);

		try {
			statement = connection.createStatement();
			statement.executeUpdate(myStatement);
		} catch (Exception e) {
			System.out.println(e);
		}
		
		System.out.println("Done");
	}

	public void executeBatch(String[] statements) throws SQLException {
		// turn off autocommit
		connection = connect();
		try {
			connection.setAutoCommit(false);
			Statement stmt = connection.createStatement();

			for (int i = 0; i < statements.length; i++) {
				stmt.addBatch(statements[i]);
			}

			// submit a batch of update commands for execution
			stmt.executeBatch();
			connection.commit();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	public Connection connect() throws SQLException {
		
	
		try {
			String driver = "org.apache.derby.jdbc.EmbeddedDriver";
			connection = DriverManager
					.getConnection("jdbc:derby:/Applications/SQuirreLSQL.app/Contents/Resources/Java/WomenVoters");
					//C:/Users/Milan/Downloads/lowv");
				//	.getConnection("jdbc:derby:C:/Users/Milan/Downloads/lowv");
			// .getConnection("jdbc:derby:C:/Users/Milan/Downloads/lowv");
			//
			// connection = DriverManager
			// .getConnection("jdbc:derby:/home/hal/squirrel-sql-3.3.0/lowv");

			Class.forName(driver).newInstance();
		} catch (Exception e) {
			System.out.println(e);
		}
		
		return connection;
		
	}

	public ResultSet getTable(String currentTable, String year) throws SQLException {
		ResultSet resultSet = null;
		
		Statement statement = null;
		Integer i = Integer.parseInt(year);
		i++;
		try {
			
			if(currentTable.equals("Member Donations")){
				statement = connection.createStatement();
			resultSet = statement
					.executeQuery("select payeepayer, CAST((sum(deposit)) + 0.005 AS DECIMAL(15,2)) as \"Contributions\" from ((select id from transhassub where name = 'Member Contributions') as t1 join (select * from transactions where date between '20"
							+ year
							+ "-01-01' and '20"
							+ year
							+ "-12-31') as t2 on t1.id = t2.id) GROUP BY PAYEEPAYER");
			}
			else if(currentTable.equals("Transactions")){
				statement = connection.createStatement();
			resultSet = statement
					.executeQuery("Select * from transactions where date between '20"
							+ year
							+ "-07-01' and '20"
							+ i
							+ "-6-30' order by date asc");
			}
			else if(currentTable.equals("Monthly Breakdown")){
				statement = connection.createStatement();
			
				resultSet = statement
						.executeQuery("Select t1.name, t1.\"July\", t2.\"August\", t3.\"September\", t4.\"October\", t5.\"November\", t6.\"December\", t7.\"January\", t8.\"Feburary\", t9.\"March\", t10.\"April\", t11.\"May\", t12.\"June\" " +
								"  from((Select name, Sum(Deposit) As \"July\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + year + "-06-30' AND '20" + year + "-08-01' group by(name)) t1 " +
								" left Join(Select name, Sum(Deposit) As \"August\" from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + year + "-07-31' AND '20" + year + "-09-01' group by(name))t2 on t2.name = t1.name left Join" +
								" (Select name, Sum(Deposit) \"September\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + year + "-08-31' AND '20" + year + "-10-01' group by(name) ) t3 on t2.name = t3.name left Join (Select name, Sum(Deposit) As \"October\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between " +
										" '20" + year + "-09-30' AND '20" + year + "-11-01' group by(name) ) t4 on t3.name = t4.name left Join  (Select name, Sum(Deposit) As \"November\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + year + "-10-31' AND '20" + year + "-12-01' group by(name)) t5 on  t4.name = t5.name " +
								" left Join  (Select name, Sum(Deposit) as \"December\" from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + year + "-11-30' AND '20" + year + "-12-31' group by(name)) t6 on t5.name = t6.name left join (Select name, Sum(Deposit) As \"January\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + i + "-12-31' AND '20" + i + "-2-01' group by(name))" +
										"t7 on t6.name = t7.name left Join (Select name, Sum(Deposit) As \"Feburary\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + i + "-1-31' AND '20" + i + "-3-01' group by(name)) t8 on t7.name = t8.name left Join (Select name, Sum(Deposit) As \"March\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + i + "-2-28' AND '20" + i + "-4-01' group by(name))t9 " +
								" on t8.name = t9.name left Join (Select name, Sum(Deposit) as \"April\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + i + "-3-31' AND '20" + i + "-5-01' group by(name))t10 on t9.name = t10.name left join (Select name, Sum(Deposit) as \"May\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + i + "-4-30' AND '20" + i + "-6-01' group by(name)) t11 on t10.name = t11.name " +
										" left join ( Select name, Sum(Deposit) as \"June\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + i + "-5-31' AND '20" + i + "-7-01' group by(name)) t12 on t11.name = t12.name)");
			}
			else{
				statement = connection.createStatement();
			resultSet = statement
					.executeQuery("Select t1.name, t1.\"July\", t2.\"August\", t3.\"September\", t4.\"October\", t5.\"November\", t6.\"December\", t7.\"January\", t8.\"Feburary\", t9.\"March\", t10.\"April\", t11.\"May\", t12.\"June\" " +
			"  from((Select name, Sum(Payment) As \"July\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + year + "-06-30' AND '20" + year + "-08-01' group by(name)) t1 " +
			" left Join(Select name, Sum(Payment) As \"August\" from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + year + "-07-31' AND '20" + year + "-09-01' group by(name))t2 on t2.name = t1.name left Join" +
			" (Select name, Sum(Payment) \"September\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + year + "-08-31' AND '20" + year + "-10-01' group by(name) ) t3 on t2.name = t3.name left Join (Select name, Sum(Payment) As \"October\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between " +
					" '20" + year + "-09-30' AND '20" + year + "-11-01' group by(name) ) t4 on t3.name = t4.name left Join  (Select name, Sum(Payment) As \"November\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + year + "-10-31' AND '20" + year + "-12-01' group by(name)) t5 on  t4.name = t5.name " +
			" left Join  (Select name, Sum(Payment) as \"December\" from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + year + "-11-30' AND '20" + year + "-12-31' group by(name)) t6 on t5.name = t6.name left join (Select name, Sum(Payment) As \"January\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + i + "-12-31' AND '20" + i + "-2-01' group by(name))" +
					"t7 on t6.name = t7.name left Join (Select name, Sum(Payment) As \"Feburary\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + i + "-1-31' AND '20" + i + "-3-01' group by(name)) t8 on t7.name = t8.name left Join (Select name, Sum(Payment) As \"March\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + i + "-2-28' AND '20" + i + "-4-01' group by(name))t9 " +
			" on t8.name = t9.name left Join (Select name, Sum(Payment) as \"April\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + i + "-3-31' AND '20" + i + "-5-01' group by(name))t10 on t9.name = t10.name left join (Select name, Sum(Payment) as \"May\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + i + "-4-30' AND '20" + i + "-6-01' group by(name)) t11 on t10.name = t11.name " +
					" left join ( Select name, Sum(Payment) as \"June\"  from transactions Join transhascat on transactions.id = transhascat.id Where date between '20" + i + "-5-31' AND '20" + i + "-7-01' group by(name)) t12 on t11.name = t12.name)" );
			}} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return resultSet;
	}

	public void resetFiscalYear() {
		Statement statement = null;
		ResultSet results = null;
		String fiscalYear = null;
		try {
			statement = connection.createStatement();
			results = statement.executeQuery("Select fiscalyear from budget where id = 1");
			results.next();
			fiscalYear = (String) results.getObject(1);
			Integer year = Integer.parseInt(fiscalYear);
			year = year + 101;
			String nextfiscalYear = Integer.toString(year);
		
			statement.executeUpdate("Update budget set fiscalyear = '" + nextfiscalYear + "' where fiscalyear = '" + fiscalYear + "'");
			statement.executeUpdate("UPDATE budget B SET previousyear = (SELECT currentyearamount FROM Budget A WHERE A.ID = B.ID)");
			statement.executeUpdate("Update budget set currentyearamount = 0.0, actualamount = 0.0");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
}
